create external table spmi_dm.dm_volume_cost_profit_income_pay_dt (
waybill_date                               date              COMMENT '运单录入日期',
entry_mark_date                            date              COMMENT '入账日期',
create_date                                date              COMMENT '账单产生日期',
destination_province_id                    String            COMMENT '目的省份id',
destination_province_name                  String            COMMENT '目的省份名称',
customer_code                              String            COMMENT '客户编码',
customer_name                              String            COMMENT '客户名称',
send_network_name                          String            COMMENT '寄件网点名称',
send_network_code                          String            COMMENT '寄件网点编码',
send_financial_center_name                 String            COMMENT '寄件代理区名称',
send_financial_center_code                 String            COMMENT '寄件代理区编码',
operation_network_name                     String            COMMENT '操作网点名称',
operation_network_code                     String            COMMENT '操作网点编码',
operation_financial_center_name            String            COMMENT '操作代理区名称',
operation_financial_center_code            String            COMMENT '操作代理区编码',
start_financial_center_name                String            COMMENT '始发代理区名称',
start_financial_center_code                String            COMMENT '始发代理区编码',
piece_network_name                         String            COMMENT '派件网点名称',
piece_network_code                         String            COMMENT '派件网点编码',
piece_financial_center_name                String            COMMENT '派件代理区名称',
piece_financial_center_code                String            COMMENT '派件代理区编码',
fee_type_name                              String            COMMENT '费用类型名称',
fee_type_code                              String            COMMENT '费用类型编码',
income_pay_type                            String            COMMENT '收支类型',
income_pay_network_attr                    String            COMMENT '收支对象属性',
income_pay_network_code                    String            COMMENT '收支对象code',
income_pay_network_name                    String            COMMENT '收支对象name',
income_pay_financial_code                  String            COMMENT '收支对象所属代理区code',
income_pay_financial_name                  String            COMMENT '收支对象所属代理区name',
piping_type_code                           String            COMMENT '管报大类编码',
piping_type_name                           String            COMMENT '管报大类名称',
waybill_votes                              bigint            COMMENT '总运单票数',
warehousing_weight_sum                     decimal(21,2)     COMMENT '总入仓重量',
cost_weight_sum                            decimal(21,2)     COMMENT '总计费重量',
settle_weight_sum                          decimal(21,2)     COMMENT '总结算重量',
fee_cost_sum                               decimal(21,6)     COMMENT '总费用',
row_id                                     bigint            COMMENT '唯一id'
)
COMMENT '量本利收入支出汇总表'
PARTITIONED BY (
       income_pay   string comment '收支属性'    ,
       table_type   string comment '源表类型'    ,
       dt STRING COMMENT '运单生成日期 (yyyy-MM-dd)' )
STORED AS PARQUET
LOCATION '/dw/hive/spmi_dm.db/external/dm_volume_cost_profit_income_pay_dt'
TBLPROPERTIES (
  'parquet.column.index.access'='true'
);


CREATE TABLE spmi_dws.dws_volume_cost_profit_income
(
     row_id                                     bigint          NULL  COMMENT '唯一主键',
     waybill_date                               date            NULL  COMMENT '运单录入日期',
     entry_mark_date                            date            NULL  COMMENT '入账日期',
     create_date                                date            NULL  COMMENT '账单产生日期',
     send_network_code                          varchar(80)     NULL  COMMENT '寄件网点编码',
     send_financial_center_code                 varchar(80)     NULL  COMMENT '寄件代理区编码',
     operation_network_code                     varchar(80)     NULL  COMMENT '操作网点编码',
     operation_financial_center_code            varchar(80)     NULL  COMMENT '操作代理区编码',
     start_financial_center_code                varchar(80)     NULL  COMMENT '始发代理区编码',
     piece_financial_center_code                varchar(80)     NULL  COMMENT '派件代理区编码',
     fee_type_code                              varchar(80)     NULL  COMMENT '费用类型编码',
     piece_network_code                         varchar(80)     NULL  COMMENT '派件网点编码',
     customer_code                              varchar(80)     NULL  COMMENT '客户编码',
     destination_province_id                    bigint          NULL  COMMENT '目的省份id',
     destination_province_name                  varchar(160)    NULL  COMMENT '目的省份名称',
     customer_name                              varchar(160)    NULL  COMMENT '客户名称',
     send_network_name                          varchar(80)     NULL  COMMENT '寄件网点名称',
     send_financial_center_name                 varchar(80)     NULL  COMMENT '寄件代理区名称',
     operation_network_name                     varchar(80)     NULL  COMMENT '操作网点名称',
     operation_financial_center_name            varchar(80)     NULL  COMMENT '操作代理区名称',
     start_financial_center_name                varchar(80)     NULL  COMMENT '始发代理区名称',
     piece_network_name                         varchar(80)     NULL  COMMENT '派件网点名称',
     piece_financial_center_name                varchar(80)     NULL  COMMENT '派件代理区名称',
     fee_type_name                              varchar(80)     NULL  COMMENT '费用类型名称',
     income_pay_type                            varchar(80)     NULL  COMMENT '收支类型',
     income_pay_network_attr                    varchar(80)     NULL  COMMENT '收支对象属性',
     income_pay_network_code                    varchar(80)     NULL  COMMENT '收支对象code',
     income_pay_network_name                    varchar(80)     NULL  COMMENT '收支对象name',
     income_pay_financial_code                  varchar(80)     NULL  COMMENT '收支对象所属代理区code',
     income_pay_financial_name                  varchar(80)     NULL  COMMENT '收支对象所属代理区name',
     piping_type_code                           varchar(80)     NULL  COMMENT '管报大类编码',
     piping_type_name                           varchar(80)     NULL  COMMENT '管报大类名称',
     waybill_votes                              bigint          NULL  COMMENT '总运单票数',
     warehousing_weight_sum                     decimal(21,2)   NULL  COMMENT '总入仓重量',
     cost_weight_sum                            decimal(21,2)   NULL  COMMENT '总计费重量',
     settle_weight_sum                          decimal(21,2)   NULL  COMMENT '总结算重量',
     fee_cost_sum                               decimal(21,6)   NULL  COMMENT '总费用'
) ENGINE = OLAP
DUPLICATE KEY(`row_id`,`waybill_date`,`entry_mark_date`,`create_date`,`send_network_code`,`send_financial_center_code`,`operation_network_code`,`operation_financial_center_code`,`start_financial_center_code`,`piece_financial_center_code`,`fee_type_code`)
COMMENT "量本利基础汇总表"
PARTITION BY RANGE(`waybill_date`)
(
PARTITION p20210801 VALUES [('2021-08-01'), ('2021-08-02')),
PARTITION p20210802 VALUES [('2021-08-02'), ('2021-08-03')),
PARTITION p20210803 VALUES [('2021-08-03'), ('2021-08-04')),
PARTITION p20210804 VALUES [('2021-08-04'), ('2021-08-05')),
PARTITION p20210805 VALUES [('2021-08-05'), ('2021-08-06')),
PARTITION p20210806 VALUES [('2021-08-06'), ('2021-08-07')),
PARTITION p20210807 VALUES [('2021-08-07'), ('2021-08-08')),
PARTITION p20210808 VALUES [('2021-08-08'), ('2021-08-09')),
PARTITION p20210809 VALUES [('2021-08-09'), ('2021-08-10')),
PARTITION p20210810 VALUES [('2021-08-10'), ('2021-08-11')),
PARTITION p20210811 VALUES [('2021-08-11'), ('2021-08-12')),
PARTITION p20210812 VALUES [('2021-08-12'), ('2021-08-13')),
PARTITION p20210813 VALUES [('2021-08-13'), ('2021-08-14')),
PARTITION p20210814 VALUES [('2021-08-14'), ('2021-08-15')),
PARTITION p20210815 VALUES [('2021-08-15'), ('2021-08-16')),
PARTITION p20210816 VALUES [('2021-08-16'), ('2021-08-17')),
PARTITION p20210817 VALUES [('2021-08-17'), ('2021-08-18')),
PARTITION p20210818 VALUES [('2021-08-18'), ('2021-08-19')),
PARTITION p20210819 VALUES [('2021-08-19'), ('2021-08-20')),
PARTITION p20210820 VALUES [('2021-08-20'), ('2021-08-21')),
PARTITION p20210821 VALUES [('2021-08-21'), ('2021-08-22')),
PARTITION p20210822 VALUES [('2021-08-22'), ('2021-08-23')),
PARTITION p20210823 VALUES [('2021-08-23'), ('2021-08-24')),
PARTITION p20210824 VALUES [('2021-08-24'), ('2021-08-25')),
PARTITION p20210825 VALUES [('2021-08-25'), ('2021-08-26')),
PARTITION p20210826 VALUES [('2021-08-26'), ('2021-08-27')),
PARTITION p20210827 VALUES [('2021-08-27'), ('2021-08-28')),
PARTITION p20210828 VALUES [('2021-08-28'), ('2021-08-29')),
PARTITION p20210829 VALUES [('2021-08-29'), ('2021-08-30')),
PARTITION p20210830 VALUES [('2021-08-30'), ('2021-08-31')),
PARTITION p20210831 VALUES [('2021-08-31'), ('2021-09-01')))
DISTRIBUTED BY HASH(`send_network_code`) BUCKETS 24
PROPERTIES (
"replication_num" = "3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.start" = "-180",
"dynamic_partition.end" = "32",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "24",
"in_memory" = "false",
"storage_format" = "v2"
);

CREATE TABLE spmi_dws.dws_volume_cost_profit_pay
(
     row_id                                     bigint          NULL  COMMENT '唯一主键',
     waybill_date                               date            NULL  COMMENT '运单录入日期',
     entry_mark_date                            date            NULL  COMMENT '入账日期',
     create_date                                date            NULL  COMMENT '账单产生日期',
     send_network_code                          varchar(80)     NULL  COMMENT '寄件网点编码',
     send_financial_center_code                 varchar(80)     NULL  COMMENT '寄件代理区编码',
     operation_network_code                     varchar(80)     NULL  COMMENT '操作网点编码',
     operation_financial_center_code            varchar(80)     NULL  COMMENT '操作代理区编码',
     start_financial_center_code                varchar(80)     NULL  COMMENT '始发代理区编码',
     piece_financial_center_code                varchar(80)     NULL  COMMENT '派件代理区编码',
     fee_type_code                              varchar(80)     NULL  COMMENT '费用类型编码',
     piece_network_code                         varchar(80)     NULL  COMMENT '派件网点编码',
     customer_code                              varchar(80)     NULL  COMMENT '客户编码',
     destination_province_id                    bigint          NULL  COMMENT '目的省份id',
     destination_province_name                  varchar(160)    NULL  COMMENT '目的省份名称',
     customer_name                              varchar(160)    NULL  COMMENT '客户名称',
     send_network_name                          varchar(80)     NULL  COMMENT '寄件网点名称',
     send_financial_center_name                 varchar(80)     NULL  COMMENT '寄件代理区名称',
     operation_network_name                     varchar(80)     NULL  COMMENT '操作网点名称',
     operation_financial_center_name            varchar(80)     NULL  COMMENT '操作代理区名称',
     start_financial_center_name                varchar(80)     NULL  COMMENT '始发代理区名称',
     piece_network_name                         varchar(80)     NULL  COMMENT '派件网点名称',
     piece_financial_center_name                varchar(80)     NULL  COMMENT '派件代理区名称',
     fee_type_name                              varchar(80)     NULL  COMMENT '费用类型名称',
     income_pay_type                            varchar(80)     NULL  COMMENT '收支类型',
     income_pay_network_attr                    varchar(80)     NULL  COMMENT '收支对象属性',
     income_pay_network_code                    varchar(80)     NULL  COMMENT '收支对象code',
     income_pay_network_name                    varchar(80)     NULL  COMMENT '收支对象name',
     income_pay_financial_code                  varchar(80)     NULL  COMMENT '收支对象所属代理区code',
     income_pay_financial_name                  varchar(80)     NULL  COMMENT '收支对象所属代理区name',
     piping_type_code                           varchar(80)     NULL  COMMENT '管报大类编码',
     piping_type_name                           varchar(80)     NULL  COMMENT '管报大类名称',
     waybill_votes                              bigint          NULL  COMMENT '总运单票数',
     warehousing_weight_sum                     decimal(21,2)   NULL  COMMENT '总入仓重量',
     cost_weight_sum                            decimal(21,2)   NULL  COMMENT '总计费重量',
     settle_weight_sum                          decimal(21,2)   NULL  COMMENT '总结算重量',
     fee_cost_sum                               decimal(21,6)   NULL  COMMENT '总费用'
) ENGINE = OLAP
DUPLICATE KEY(`row_id`,`waybill_date`,`entry_mark_date`,`create_date`,`send_network_code`,`send_financial_center_code`,`operation_network_code`,`operation_financial_center_code`,`start_financial_center_code`,`piece_financial_center_code`,`fee_type_code`)
COMMENT "量本利基础汇总表"
PARTITION BY RANGE(`waybill_date`)
(
PARTITION p20210801 VALUES [('2021-08-01'), ('2021-08-02')),
PARTITION p20210802 VALUES [('2021-08-02'), ('2021-08-03')),
PARTITION p20210803 VALUES [('2021-08-03'), ('2021-08-04')),
PARTITION p20210804 VALUES [('2021-08-04'), ('2021-08-05')),
PARTITION p20210805 VALUES [('2021-08-05'), ('2021-08-06')),
PARTITION p20210806 VALUES [('2021-08-06'), ('2021-08-07')),
PARTITION p20210807 VALUES [('2021-08-07'), ('2021-08-08')),
PARTITION p20210808 VALUES [('2021-08-08'), ('2021-08-09')),
PARTITION p20210809 VALUES [('2021-08-09'), ('2021-08-10')),
PARTITION p20210810 VALUES [('2021-08-10'), ('2021-08-11')),
PARTITION p20210811 VALUES [('2021-08-11'), ('2021-08-12')),
PARTITION p20210812 VALUES [('2021-08-12'), ('2021-08-13')),
PARTITION p20210813 VALUES [('2021-08-13'), ('2021-08-14')),
PARTITION p20210814 VALUES [('2021-08-14'), ('2021-08-15')),
PARTITION p20210815 VALUES [('2021-08-15'), ('2021-08-16')),
PARTITION p20210816 VALUES [('2021-08-16'), ('2021-08-17')),
PARTITION p20210817 VALUES [('2021-08-17'), ('2021-08-18')),
PARTITION p20210818 VALUES [('2021-08-18'), ('2021-08-19')),
PARTITION p20210819 VALUES [('2021-08-19'), ('2021-08-20')),
PARTITION p20210820 VALUES [('2021-08-20'), ('2021-08-21')),
PARTITION p20210821 VALUES [('2021-08-21'), ('2021-08-22')),
PARTITION p20210822 VALUES [('2021-08-22'), ('2021-08-23')),
PARTITION p20210823 VALUES [('2021-08-23'), ('2021-08-24')),
PARTITION p20210824 VALUES [('2021-08-24'), ('2021-08-25')),
PARTITION p20210825 VALUES [('2021-08-25'), ('2021-08-26')),
PARTITION p20210826 VALUES [('2021-08-26'), ('2021-08-27')),
PARTITION p20210827 VALUES [('2021-08-27'), ('2021-08-28')),
PARTITION p20210828 VALUES [('2021-08-28'), ('2021-08-29')),
PARTITION p20210829 VALUES [('2021-08-29'), ('2021-08-30')),
PARTITION p20210830 VALUES [('2021-08-30'), ('2021-08-31')),
PARTITION p20210831 VALUES [('2021-08-31'), ('2021-09-01')))
DISTRIBUTED BY HASH(`send_network_code`) BUCKETS 24
PROPERTIES (
"replication_num" = "3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.start" = "-180",
"dynamic_partition.end" = "32",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "24",
"in_memory" = "false",
"storage_format" = "v2"
);
